しがないOLのはじめてのMySQL（オートナンバーの設定）
はじめに
こんにちは。クニ吉です。
昨日フレンチプレス式コーヒーメーカーが届きました。
早速ハワイで買ったコナコーヒーを淹れてみたところ、とても美味しくできてました。
電動ミルも買ったので、次はカウコーヒーを挽いて飲んでみます。
さて、今回も引き続きMySQLについて。
前回さらっと書いていたオートナンバーについて書いてみようと思います。
前提
本ブログにおけるMySQLのバージョンは「5.7.17」です。
過去ブログで既に記載している構文や結果表示については、さらっと流して書いていますので、過去ブログをご確認ください。
本ブログの構成
- オートナンバー改めAUTO INCREMENTについて
- AUTO INCREMENTの設定方法
- AUTO INCREMENTの採番について
- AUTO INCREMENTの採番をリセットする方法
- おわりに
オートナンバー改めAUTO INCREMENTについて
概要
オートナンバーというとどういうものかすぐにイメージがつきやすいので書きましたが、MySQLでは「AUTO INCREMENT(オートインクリメント)」という設定になります。
「自動増加」という意味で、AUTO INCREMENTを設定したカラムに自動で番号を割り当てていきます。
デフォルト設定では値は1から始まり、データを追加する度に+1した値がAUTO INCREMENTに設定したカラムにデータとして挿入されます。
実践
しがないOLのはじめてのMySQLの「データ投入」の項では、以下のようにidを指定していました。
mysql> insert into item(id, item_name, price)values(1, 'カエルのオルゴール', 3500);
AUTO INCREMENTを設定している場合には、自動で番号を割り振ってくれるので、このid部分の記述を省略することができます。
idを省略してデータを追加：
データ追加：
mysql> insert into item(item_name, price)values('コナコーヒー7oz', 2999);
Query OK, 1 row affected (0.00 sec)
テーブルの中身を確認：
mysql> select * from item;
+----+-----------------------+-------+
| id | item_name | price |
+----+-----------------------+-------+
| 1 | コナコーヒー7oz | 2999 |
+----+-----------------------+-------+
1 row in set (0.00 sec)
このようにidを指定することも可能です。
idを指定してデータを追加：
データ追加： mysql> insert into item(id, item_name, price) values(3, 'カウコーヒー8oz', 4212); Query OK, 1 row affected (0.01 sec) テーブルの中身を確認： mysql> select * from item; +----+-----------------------+-------+ | id | item_name | price | +----+-----------------------+-------+ | 1 | コナコーヒー7oz | 2999 | | 3 | カウコーヒー8oz | 4212 | +----+-----------------------+-------+ 2 rows in set (0.00 sec)
AUTO INCREMENTの設定方法
概要
では、AUTO INCREMENTの設定をしたいと思いますが、その前にAUTO INCREMENTを設定するための条件があります。
- AUTO INCREMENTはテーブル内に1つしか設定できない
- AUTO INCREMENTを設定するカラムはPRIMARY KEY または UNIQUE KEYである必要がある
- AUTO INCREMENTのを設定したカラムには正の値しか格納できない
PRIMARY KEY（主キー）とは
しがないOLのはじめてのMySQLの「MySQLとは」の項でも書かせていただきましたが、MySQLはリレーショナルデータベースを扱います。リレーショナルデータベースは以下の表のような構造でデータを管理します。
| id | name | age |
|---|---|---|
| 1 | 花子 | 10 |
| 2 | 太郎 | 7 |
テーブルの各データ（組、レコード）を識別するための値として利用されるのが「PRIMARY KEY」です。
PRIMARY KEYは以下の特性をもっています。
- UNIQUE KEYである（値の重複を許さない）
- NOT NULLが設定される（NULL値の格納を許さない）
- INDEXを作成する（データの検索）
- PRIMARY KEYはテーブルに1つしか設定できない
PRIMARY KEYはデータの住所のようなもので、上記の例でいうと「id」をPRIMARY KEYとして設定します。 テーブル内に「name=花子」というデータが複数あったとしても、データベースにおいてはidが「1」の花子さんと、「10」の花子さんは別人である、ということになります。
PRIMARY KEYは、複数のカラムをひとつのPRIMARY KEYとして扱うこともできます。
これを「複合主キー」といいますが、これはまた後日どこかで・・。
UNIQUE KEYとは
UNIQUE KEYはPRIMARY KEYの項でも書きましたが、「重複を許さない」ことだけです。
以下はPRIMARY KEY と比較したUNIQUE KEYの特性です。
- UNIQUE KEYである（値の重複を許さない）
- NULL値を格納できる（NOT NULLを設定することもできる）
- INDEXを作成しない（INDEXを作成することもできる）
- テーブルに複数設定できる
実践
長くなったので、そろそろ実践。
テーブルを新規作成する場合：
CREATE TABLE文でAUTO INCREMENT と PRIMARY KEYの設定をします。
構文：CREATE TABLE {テーブル名} ({カラム名} {データ型} AUTO_INCREMENT, {カラム名} {データ型}, …, PRIMARY KEY({KEYを設定するカラム名}));
mysql> create table item(id int auto_increment, item_name varchar(20), price int, primary key(id));
すでに作成済みのテーブルに設定する場合：
1.先にKEYの設定をする必要がありますので、ALTER TABLE文でPRIMARY KEYの設定をします。
構文：ALTER TABLE {テーブル名} ADD PRIMARY KEY({KEYを設定するカラム名})
mysql> alter table item add primary key(id);
これで「id」がPRIMARY KEYになりました。
2.ALTER TABLE文でAUTO INCREMENTを設定します。
構文：ALTER TABLE {テーブル名} MODIFY {AUTO INCREMENTを設定するカラム名} {カラムのデータ型} AUTO_INCREMENT;
mysql> alter table item modify id int auto_increment;
これでidにAUTO INCREMENTを設定することができました。
AUTO INCREMENTの採番について
概要
AUTO INCREMENTを設定すると自動採番してくれることがわかりましたが、 では、「id = 3」が割り当てられた後や、最後に登録したデータを削除した場合、どう採番されていくのでしょうか。
答えを先に言ってしまうと、テーブルに登録したことのある最大値に+1した番号が割り当てられます。
- 「id = 3」が割り当てられた後、追加するデータのidは「4」が割り当てられます。
- 「id = 4」のデータを削除した後、追加するデータのidは「5」が割り当てられます。
実践
idを省略して再度データ追加：
データ追加：
mysql> insert into item(item_name, price) values('ブルーマウンテン200g', 3300);
Query OK, 1 row affected (0.00 sec)
テーブルの中身を確認：
mysql> select * from item;
+----+------------------------------+-------+
| id | item_name | price |
+----+------------------------------+-------+
| 1 | コナコーヒー7oz | 2999 |
| 3 | カウコーヒー8oz | 4212 |
| 4 | ブルーマウンテン200g | 3300 |
+----+------------------------------+-------+
3 rows in set (0.00 sec)
データ追加前は「id = 3」が最大値になるので、+1した「4」がidに割り当てられました。
「id = 4」のデータを削除して、再度データ追加：
データ削除：
mysql> delete from item where id = 4;
Query OK, 1 row affected (0.00 sec)
テーブルの中身を確認：
mysql> select * from item;
+----+-----------------------+-------+
| id | item_name | price |
+----+-----------------------+-------+
| 1 | コナコーヒー7oz | 2999 |
| 3 | カウコーヒー8oz | 4212 |
+----+-----------------------+-------+
2 rows in set (0.00 sec)
データ追加：
mysql> insert into item(item_name, price) values('ブルーマウンテン200g', 3300);
Query OK, 1 row affected (0.00 sec)
テーブルの中身を確認：
mysql> select * from item;
+----+------------------------------+-------+
| id | item_name | price |
+----+------------------------------+-------+
| 1 | コナコーヒー7oz | 2999 |
| 3 | カウコーヒー8oz | 4212 |
| 5 | ブルーマウンテン200g | 3300 |
+----+------------------------------+-------+
3 rows in set (0.00 sec)
「id = 4」のデータを削除しましたが、新しく追加したデータのidは「5」が割り当てられました。 これはテーブルに存在しているデータの最大値ではなく、過去に登録されたことがあるデータの最大値を取っているためです。
テーブルの詳細を確認してみましょう。
※「SHOW TABLE STATUS \G」でテーブルの状態を確認できます。（\Gは縦表示にするオプションです。）
mysql> show table status \G
*************************** 1. row ***************************
Name: item
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 6
Create_time: 2017-05-23 15:04:17
Update_time: 2017-05-23 15:20:59
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
「Rows: 3」と「Auto increment: 6」という項目がポイントです。
- Rows：データ行数（現在テーブルが保持しているデータ数）を表示
- Auto increment：AUTO INCREMENTが次に割り当てるべき番号を表示
1件のデータを削除した場合、「Rows」は-1された値に変更になりますが、「Auto increment」は変更されません。
AUTO INCREMENTはデータが追加された時に、AUTO INCREMENTが保持している値に+1するため、結果的に過去に登録されていた番号の最大値に+1する数が割り当てられることになります。
AUTO INCREMENTの採番をリセットする方法
「AUTO INCREMENT」の採番をリセットしたい場合は、以下の方法で行います。
手順
全データを削除してリセットする：
前回のしがないOLのはじめてのMySQL（データの削除）でご紹介した「TRUNCATE文」を使います。
データを削除せず、途中から番号をリセットする：
仮に、誤って「id = 4」のデータを削除してしまって、再度idを「4」で再登録したい場合は、強制的に「AUTO INCREMENT」の値を書き換えます。
1.「id = 5」のデータを削除し、テーブルの中身を確認します。
データ削除： mysql> delete from item where id = 5; Query OK, 1 row affected (0.00 sec) テーブルの中身を確認： mysql> select * from item; +----+-----------------------+-------+ | id | item_name | price | +----+-----------------------+-------+ | 1 | コナコーヒー7oz | 2999 | | 3 | カウコーヒー8oz | 4212 | +----+-----------------------+-------+ 2 rows in set (0.00 sec)
この時、AUTO INCREMENTは「6」のままです。
2.再登録するデータのidを「4」にしたいので、ALTER TABLE文でテーブル構造を書き換えます。
構文：ALTER TABLE {テーブル名} AUTO_INCREMENT = {次回登録時に設定したい番号}
mysql> alter table item auto_increment = 4;
結果：
Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
SHOW TABLE STATUSでテーブルの情報を確認すると、AUTO INCREMENTが「4」になっていることが確認できます。
mysql> show table status \G;
*************************** 1. row ***************************
Name: item
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 4
Create_time: 2017-05-23 16:46:36
Update_time: 2017-05-23 16:37:55
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
3.再度「ブルーマウンテン200g」を追加して、テーブルの中身を確認します。
データ追加：
mysql> insert into item(item_name, price) values('ブルーマウンテン200g', 3300);
Query OK, 1 row affected (0.00 sec)
テーブルの中身を確認：
mysql> select * from item;
+----+------------------------------+-------+
| id | item_name | price |
+----+------------------------------+-------+
| 1 | コナコーヒー7oz | 2999 |
| 3 | カウコーヒー8oz | 4212 |
| 4 | ブルーマウンテン200g | 3300 |
+----+------------------------------+-------+
3 rows in set (0.00 sec)
idが「4」で登録されました。
このように、AUTO INCREMENTの値を書き換えることで採番をリセットすることができます。
おわりに
AUTO INCREMENTについては以上になります。
ブログを書くにあたって、PRIMARY KEYなどについて整理できたのが収穫でした。
余談ですが、今回登録するデータにコーヒーを使ってみたのですが、Amazonで値段見てびっくりしました。
こっちで買おうとすると高いんですねぇ・・・。
せっかくミルを買ったので、いろいろな豆を試したいと思います(^^)
ではでは。
